package com.yc.api.d1023;


import java.sql.*;
import java.util.Scanner;

public class EmpManager {
    static Scanner sc = new Scanner (System.in);

    public static void main(String[] args) {

        EmpManager em = new EmpManager();

        while(true) {
            System.out.println("*************************");
            System.out.println("*      1.查询           *");
            System.out.println("*      2.新增           *");
            System.out.println("*      3.修改           *");
            System.out.println("*      4.删除           *");
            System.out.println("*      0.退出           *");
            System.out.println("*************************");
            System.out.println("请输入操作编号：");
            String input = sc.nextLine();
            switch(input) {
                case"1":
                    em.query();
                    break;
                case"2":
                    em.add();
                    break;
                case"3":
                    em.update();
                    break;
                case"4":
                    em.delete();
                    break;
                case"0":
                    System.out.println("退出成功");
                    return;
            }

        }

    }

    public void delete() {
        try (Connection conn = getConnection()){
            System.out.print("请输入需要删除员工的编号:");
            String empno = sc.nextLine();
            //查出要删除的员工是否有下级
            String sql1= "select empno from emp where mgr=?";
            PreparedStatement ps = conn.prepareStatement(sql1);
            ps.setString(1,empno);
            ResultSet rs = ps.executeQuery();
            //===========================================
            //对结果集遍历
            //next控制游标(记录指针)向下走，如果有下一行数据，返回true，否则false
            int empno1 = -44;
            while (rs.next()) {
                //获取第一个字段的值empno
                empno1 = rs.getInt(1);//jdbc序号从1开始
            }
            if(empno1 == -44) {
                String sql = "delete from emp where empno=?";
                PreparedStatement ps1 = conn.prepareStatement(sql);
                ps1.setString(1,empno);
                System.out.println("删除成功！");
                ps1.executeUpdate();
                conn.commit();
            }else {
                System.out.println("该员工有下属，无法删除！");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


    public void add() {
        try (Connection conn = getConnection()){
            System.out.print("请输入新员工编号:");
            String empno = sc.nextLine();
            System.out.print("请输入新员工姓名:");
            String ename = sc.nextLine();
            System.out.print("请输入新员工职位:");
            String job = sc.nextLine();
            System.out.print("请输入新员工领导编号:");
            String mgr = sc.nextLine();
            System.out.print("请输入新员工入职时间(格式yyyy-MM-dd): ");
            String s = sc.nextLine();
            Date date = Date.valueOf(s);
            System.out.print("请输入新员工薪资:");
            String sal = sc.nextLine();
            System.out.print("请输入新员工补贴:");
            String comm = sc.nextLine();
            System.out.print("请输入新员工部门:");
            String deptno = sc.nextLine();
            //要验证mgr是否合法
            String sql = "insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)"
                    + "values(?,?,?,?,?,?,?,?)";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, empno);
            ps.setString(2, ename);
            ps.setString(3, job);
            ps.setString(4, mgr);
            ps.setDate  (5, date);
            ps.setString(6, sal);
            ps.setString(7, comm);
            ps.setString(8, deptno);
            ps.executeUpdate();
            System.out.println("新员工添加成功!");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


    public void update() {
        try (Connection conn = getConnection()){
            System.out.println("请输入需要修改员工信息的编号：");
            String empno = sc.nextLine();

            String sql = "select ename from emp where empno=?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1,empno);
            ResultSet rs = ps.executeQuery();
            rs.next();

            String ename1 = rs.getNString("ENAME");
            System.out.println("您要修改的员工姓名为:" + ename1);
            System.out.print("请输入新的员工编号:");
            String empno1 = sc.nextLine();
            System.out.print("请输入新的员工姓名:");
            String ename = sc.nextLine();

            String sql2 = "select empno from emp where ename =?";
            PreparedStatement ps2 = conn.prepareStatement(sql2);
            ps2.setString(1, ename);
            ResultSet rs2 = ps2.executeQuery();

            int Empno = -44;
            while(rs2.next()) {
                Empno = rs.getInt(1);
                System.out.println(Empno);
            }if(Empno == -44) {
                System.out.print("请输入新的员工职位:");
                String job = sc.nextLine();
                System.out.print("请输入新的员工领导编号:");
                String mgr = sc.nextLine();
                System.out.print("请输入新的员工入职时间(格式yyyy-MM-dd): ");
                String s = sc.nextLine();
                Date date = Date.valueOf(s);
                System.out.print("请输入新的员工薪资:");
                String sal = sc.nextLine();
                Double ssal=Double.parseDouble(sal);
                if(ssal > 800 ) {
                    System.out.print("请输入新的员工补贴:");
                    String comm = sc.nextLine();
                    System.out.print("请输入新的员工部门:");
                    String deptno = sc.nextLine();
                    String sql1 = "update emp set empno=?,ename=?,job=?,mgr=?,hiredate=?,sal=?,comm=?,deptno=? where empno=?";

                    PreparedStatement ps1 = conn.prepareStatement(sql1);
                    ps1.setString(1, empno1);
                    ps1.setString(2, ename);
                    ps1.setString(3, job);
                    ps1.setString(4, mgr);
                    ps1.setDate  (5, date);
                    ps1.setString(6, sal);
                    ps1.setString(7, comm);
                    ps1.setString(8, deptno);
                    ps1.setString(9, empno);
                    ps1.executeUpdate();
                    System.out.println("员工信息修改成功!");
                }else {
                    System.out.println("工资不能低于最低工资800!");
                }
            }else {
                System.out.println("输入的新员工姓名有重名！");
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


    public void query() {
        //jdk1.8新语法conn 可以自动被关闭
        //所有实现了AutoCloseable 的类才能被自动关闭
        try (Connection conn = getConnection();){
            String sql = "select a.*,b.ename mgrname from emp a join emp b on a.mgr=b.empno";
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            System.out.printf("%-10s %-10s %-10s %-10s %-10s %-10s %-10s %-10s %-10s \n",
                    //jdbc可以实现类型的自动转换
                    "编号",
                    "姓名",
                    "职位",
                    "领导编号",
                    "入职时间",
                    "薪资",
                    "奖金",
                    "部门",
                    "领导姓名");
            while(rs.next()) {
                System.out.printf("%-10s  %-10s  %-10s     %-10s %-10s      %-10s   %-10s   %-10s   %-10s \n",
                        //jdbc可以实现类型的自动转换
                        rs.getString("EMPNO"),
                        rs.getString("ENAME"),
                        rs.getString("JOB"),
                        rs.getString("MGR"),
                        rs.getDate("HIREDATE"),
                        rs.getString("SAL"),
                        rs.getString("COMM"),
                        rs.getString("DEPTNO"),
                        rs.getString("MGRNAME"));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);		}
    }


    public Connection getConnection() {
        try {
            //加载驱动
            Class.forName("oracle.jdbc.driver.OracleDriver");
            //创建连接
            return DriverManager.getConnection(
                    "jdbc:oracle:thin:@127.0.0.1:1521/orcl","scott","1");
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}
